package data.UserData;

import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import po.userPO.AdministratorPO;
import po.userPO.DepartmentDeanPO;
import po.userPO.StudentPO;
import po.userPO.TeacherPO;
import po.userPO.UniversityDeanPO;
import po.userPO.UserPO;
import dataservice.ResultMessage;
import dataservice.userDataService.UserDatabaseService;

public class UserData extends UnicastRemoteObject implements
		UserDatabaseService {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public UserData() throws RemoteException {
		super();
	}

	@Override
	public ResultMessage insert(UserPO po) {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = convert_to_sqlinsert(po);
			System.out.println(sqlString);
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			if (e.getErrorCode() == 1062) {
				return ResultMessage.INSERT_DEPLICATE_PRIMARY;
			}
			if (e.getErrorCode() == 1364) {
				return ResultMessage.INSERT_ISNULL;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.INSERT_SUCCESS;
		} else {
			return ResultMessage.INSERT_FAILURE;
		}
	}

	@Override
	public ResultMessage delete(UserPO po) {
		int result = -1;
		String tableString = getTable(po.getUserType());
		String idField = getIDField(po.getUserType());
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "DELETE FROM `选课管理系统`.`" + tableString
					+ "` WHERE `" + idField + "` = '" + po.getId() + "'";
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.DELETE_SUCCESS;
		} else {
			return ResultMessage.DELETE_FAILURE;
		}
	}

	@Override
	public ResultMessage update(UserPO po) {
		switch (po.getUserType()) {
		case UserPO.STUDENT:
			StudentPO studentPO = (StudentPO) po;
			return updateStudent(studentPO);
		case UserPO.TEACHER:
			TeacherPO teacherPO = (TeacherPO) po;
			return updateTeacher(teacherPO);
		case UserPO.DEPARTMENTDEAN:
			DepartmentDeanPO departmentDeanPO = (DepartmentDeanPO) po;
			return updateDepartmentDean(departmentDeanPO);
		case UserPO.UNIVERSITYDEAN:
			UniversityDeanPO universityDeanPO = (UniversityDeanPO) po;
			return updateUniversityDean(universityDeanPO);
		default:
			break;
		}
		return null;
	}

	@Override
	public UserPO find(String id) {
		UserPO userPO = null;
		userPO = findStudent(id);
		if (userPO != null) {
			return userPO;
		}
		userPO = findTeacher(id);
		if (userPO != null) {
			return userPO;
		}
		userPO = findDepartmentDean(id);
		if (userPO != null) {
			return userPO;
		}
		userPO = findUniversityDean(id);
		if (userPO != null) {
			return userPO;
		}
		userPO = findAdmin(id);
		if (userPO!=null){
			return userPO;
		}
		return null;
	}



	@Override
	public ArrayList<UserPO> findAll(int userType) {
		switch (userType) {
		case UserPO.STUDENT:
			return findAllStudent();
		case UserPO.TEACHER:
			return findAllTeacher();
		case UserPO.DEPARTMENTDEAN:
			return findAllDepartmentDean();
		case UserPO.UNIVERSITYDEAN:
			return findAllUniversityDean();
		default:
			break;
		}
		return null;
	}

	@Override
	public ResultMessage update(String id, String password) {
		int result = -1;
		int userType = findUserType(id);
		if (userType == -1) {
			return ResultMessage.UPDATE_FAILURE;
		}
		String fieldString = getPWField(userType);
		String idField = getIDField(userType);
		String tableString = getTable(userType);
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "UPDATE `选课管理系统`.`" + tableString + "` SET `"
					+ fieldString + "` = '" + password + "'";
			sqlString = sqlString + " WHERE `" + idField + "` = '" + id + "'";
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			if (e.getErrorCode() == 1452) {
				return ResultMessage.UPDATE_FOREIGNKEY_NOT_EXIST;
			}
			if (e.getErrorCode() == 1062) {
				return ResultMessage.UPDATE_DEPLICATE_PRIMARY;
			}
			if (e.getErrorCode() == 1048) {
				return ResultMessage.UPDATE_ISNULL;
			}
			if (e.getErrorCode() == 1406) {
				return ResultMessage.UPDATE_TOO_LONG;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.UPDATE_SUCCESS;
		} else {
			return ResultMessage.UPDATE_FAILURE;
		}
	}

	@Override
	public ArrayList<UserPO> findAllByDepartment(int userType, String department)
			throws RemoteException {
		switch (userType) {
		case UserPO.STUDENT:
			return findAllStudentByDepartment(department);
		case UserPO.TEACHER:
			return findAllTeacherByDepartment(department);
		case UserPO.DEPARTMENTDEAN:
			return findAllDepartmentDeanByDepartment(department);
		case UserPO.UNIVERSITYDEAN:
			return null;
		default:
			break;
		}
		return null;
	}

//	private enum UserType {
//		USER, ADMINISTRATOR, TEACHER, STUDENT, DEPARTMENTDEAN, UNIVERSITYDEAN;
//	}
//
//	private enum UserField {
//		ID, NAME, PASSWORD, DEPARTMENTID,
//		// student
//		GPA,
//	}

	private String convert_to_sqlinsert(UserPO userPO) {
		String result = null;
		switch (userPO.getUserType()) {
		case UserPO.STUDENT:
			StudentPO studentPO = (StudentPO) userPO;
			result = student_convert_to_sqlStatement(studentPO);
			break;
		case UserPO.TEACHER:
			TeacherPO teacherPO = (TeacherPO) userPO;
			result = teacher_convert_to_sqlStatement(teacherPO);
			break;
		case UserPO.DEPARTMENTDEAN:
			DepartmentDeanPO departmentDeanPO = (DepartmentDeanPO) userPO;
			result = departmentDean_convert_to_sqlStatement(departmentDeanPO);
			break;
		case UserPO.UNIVERSITYDEAN:
			UniversityDeanPO universityDeanPO = (UniversityDeanPO) userPO;
			result = universityDean_convert_to_sqlStatement(universityDeanPO);
			break;
		default:
			break;
		}
		return result;
	}

	private String student_convert_to_sqlStatement(StudentPO studentPO) {
		String head = "INSERT INTO `选课管理系统`.`student`";
		String colomnString = "(`stuID`,`stuName`,`stuPassword`,`departName`,`GPA`,`totalCredit`,`totalSelectedCredit`,`grade`)";
		String dataString = "VALUES (" + getValue(studentPO.getId());
		dataString = dataString + "," + getValue(studentPO.getName());
		dataString = dataString + "," + getValue(studentPO.getPassword());
		dataString = dataString + "," + getValue(studentPO.getDepartmentId());
		dataString = dataString + "," + "'"
				+ changeToString(studentPO.getGPA()) + "'";
		dataString = dataString + "," + "'"
				+ Integer.toString(studentPO.getTotalCredit()) + "'";
		dataString = dataString + "," + "'"
				+ Integer.toString(studentPO.getTotalSelectedCredit()) + "'";
		dataString = dataString+ ",'"+Integer.toString(studentPO.getGrade())+"')";
		return head + colomnString + dataString;
	}

	private String teacher_convert_to_sqlStatement(TeacherPO teacherPO) {
		String head = "INSERT INTO `选课管理系统`.`teacher`";
		String colomnString = "(`teaID`,`teaName`,`teaPassword`,`departName`)";
		String dataString = "VALUES (" + getValue(teacherPO.getId());
		dataString = dataString + "," + getValue(teacherPO.getName());
		dataString = dataString + "," + getValue(teacherPO.getPassword());
		dataString = dataString + "," + getValue(teacherPO.getDepartmentId())
				+ ")";
		return head + colomnString + dataString;
	}

	private String departmentDean_convert_to_sqlStatement(
			DepartmentDeanPO departmentDeanPO) {
		String head = "INSERT INTO `选课管理系统`.`departmentdean`";
		String colomnString = "(`depID`,`depName`,`depPassword`,`departName`)";
		String dataString = "VALUES (" + getValue(departmentDeanPO.getId());
		dataString = dataString + "," + getValue(departmentDeanPO.getName());
		dataString = dataString + ","
				+ getValue(departmentDeanPO.getPassword());
		dataString = dataString + ","
				+ getValue(departmentDeanPO.getDepartmentId()) + ")";
		return head + colomnString + dataString;
	}

	private String universityDean_convert_to_sqlStatement(
			UniversityDeanPO universityDeanPO) {
		String head = "INSERT INTO `选课管理系统`.`universitydean`";
		String colomnString = "(`uniID`,`uniName`,`uniPassword`)";
		String dataString = "VALUES (" + getValue(universityDeanPO.getId());
		dataString = dataString + "," + getValue(universityDeanPO.getName());
		dataString = dataString + ","
				+ getValue(universityDeanPO.getPassword()) + ")";
		return head + colomnString + dataString;
	}

	private String getValue(String valueString) {
		String result = null;
		if (!((valueString == null) || (valueString.isEmpty()))) {
			result = "'" + valueString + "'";
		} else {
			result = "NULL";
		}
		return result;
	}

	private String getIDField(int userType) {
		String result = null;
		switch (userType) {
		case UserPO.STUDENT:
			result = "stuID";
			break;
		case UserPO.TEACHER:
			result = "teaID";
			break;
		// case UserPO.ADMINISTRATOR:
		// break;
		case UserPO.UNIVERSITYDEAN:
			result = "uniID";
			break;
		case UserPO.DEPARTMENTDEAN:
			result = "depID";
			break;
		default:
			break;
		}
		return result;
	}

	private String getPWField(int userType) {
		String result = null;
		switch (userType) {
		case UserPO.STUDENT:
			result = "stuPassword";
			break;
		case UserPO.TEACHER:
			result = "teaPassword";
			break;
		// case UserPO.ADMINISTRATOR:
		// break;
		case UserPO.UNIVERSITYDEAN:
			result = "uniPassword";
			break;
		case UserPO.DEPARTMENTDEAN:
			result = "depPassword";
			break;
		default:
			break;
		}
		return result;
	}

	private String getTable(int userType) {
		String result = null;
		switch (userType) {
		case UserPO.STUDENT:
			result = "student";
			break;
		case UserPO.TEACHER:
			result = "teacher";
			break;
		case UserPO.DEPARTMENTDEAN:
			result = "departmentdean";
			break;
		case UserPO.UNIVERSITYDEAN:
			result = "universitydean";
			break;
		default:
			break;
		}
		return result;
	}

	private StudentPO findStudent(String id) {
		String stuID = null;
		String stuName = null;
		String pw = null;
		String stuDepart = null;
		int totalCredit = 0;
		int totalSelectedCredit = 0;
		HashMap<Integer, Double> gpa = null;
		int grade = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			ResultSet rs = statement
					.executeQuery("SELECT * FROM `选课管理系统`.`student` WHERE stuID='"
							+ id + "'");
			if (rs.next()) {
				stuID = rs.getString(1);
				stuName = rs.getString(2);
				pw = rs.getString(3);
				stuDepart = rs.getString(4);
				gpa = changeToHashMap(rs.getString(5));
				totalCredit = rs.getInt(6);
				totalSelectedCredit = rs.getInt(7);
				grade = rs.getInt(8);
			} else {
				return null;
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		StudentPO studentPO = new StudentPO(stuID, stuName, pw,  stuDepart,
				totalCredit, totalSelectedCredit, grade);
		studentPO.setGPA(gpa);
		return studentPO;
	}

	private TeacherPO findTeacher(String id) {
		String teaID = null;
		String teaName = null;
		String pw = null;
		String teaDepart = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			ResultSet rs = statement
					.executeQuery("SELECT * FROM `选课管理系统`.`teacher` WHERE teaID='"
							+ id + "'");
			if (rs.next()) {
				teaID = rs.getString(1);
				teaName = rs.getString(2);
				pw = rs.getString(3);
				teaDepart = rs.getString(4);
			} else {
				return null;
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		TeacherPO teacherPO = new TeacherPO(teaID, teaName, pw, teaDepart);
		return teacherPO;
	}

	private DepartmentDeanPO findDepartmentDean(String id) {
		String depID = null;
		String depName = null;
		String pw = null;
		String depDepart = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString="SELECT * FROM `选课管理系统`.`departmentdean` WHERE depID='"
					+ id + "'";
			ResultSet rs = statement
					.executeQuery(sqlString);
			if (rs.next()) {
				depID = rs.getString(1);
				depName = rs.getString(2);
				pw = rs.getString(3);
				depDepart = rs.getString(4);
			} else {
				return null;
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		DepartmentDeanPO departmentDeanPO = new DepartmentDeanPO(depID,
				depName, pw, depDepart);
		return departmentDeanPO;
	}

	private UniversityDeanPO findUniversityDean(String id) {
		String uniID = null;
		String uniName = null;
		String pw = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			ResultSet rs = statement
					.executeQuery("SELECT * FROM `选课管理系统`.`universitydean` WHERE uniID='"
							+ id + "'");
			
			if (rs.next()) {
				uniID = rs.getString(1);
				uniName = rs.getString(2);
				pw = rs.getString(3);
			} else {
				return null;
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println(uniName);
		UniversityDeanPO universityDeanPO = new UniversityDeanPO(uniID,
				uniName, pw);
		return universityDeanPO;
	}

	private UserPO findAdmin(String id) {
		String admID = null;
		String admName = null;
		String pw = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			ResultSet rs = statement
					.executeQuery("SELECT * FROM `选课管理系统`.`admin` WHERE admID='"
							+ id + "'");
			
			if (rs.next()) {
				admID = rs.getString(1);
				admName = rs.getString(2);
				pw = rs.getString(3);
			} else {
				return null;
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		AdministratorPO administratorPO = new AdministratorPO(admID,
				admName, pw);
		return administratorPO;
	}
	
	private ArrayList<UserPO> findAllStudent() {
		ArrayList<UserPO> studentPOs = new ArrayList<UserPO>();
		String stuID = null;
		String stuName = null;
		String pw = null;
		String stuDepart = null;
		int totalCredit = 0;
		int totalSelectedCredit = 0;
		HashMap<Integer, Double> gpa = null;
		int grade = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			ResultSet rs = statement
					.executeQuery("SELECT * FROM `选课管理系统`.`student`");
			while (rs.next()) {
				stuID = rs.getString(1);
				stuName = rs.getString(2);
				pw = rs.getString(3);
				stuDepart = rs.getString(4);
				gpa = changeToHashMap(rs.getString(5));
				totalCredit = rs.getInt(6);
				totalSelectedCredit = rs.getInt(7);
				grade = rs.getInt(8);
				StudentPO studentPO = new StudentPO(stuID, stuName, pw, 
						stuDepart, totalCredit, totalSelectedCredit, grade);
				studentPO.setGPA(gpa);
				studentPOs.add(studentPO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return studentPOs;
	}

	private ArrayList<UserPO> findAllTeacher() {
		ArrayList<UserPO> teacherPOs = new ArrayList<UserPO>();
		String teaID = null;
		String teaName = null;
		String pw = null;
		String teaDepart = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			ResultSet rs = statement
					.executeQuery("SELECT * FROM `选课管理系统`.`teacher`");
			while (rs.next()) {
				teaID = rs.getString(1);
				teaName = rs.getString(2);
				pw = rs.getString(3);
				teaDepart = rs.getString(4);
				TeacherPO teacherPO = new TeacherPO(teaID, teaName, pw,
						teaDepart);
				teacherPOs.add(teacherPO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return teacherPOs;
	}

	private ArrayList<UserPO> findAllDepartmentDean() {
		ArrayList<UserPO> departmentDeanPOs = new ArrayList<UserPO>();
		String depID = null;
		String depName = null;
		String pw = null;
		String depDepart = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			ResultSet rs = statement
					.executeQuery("SELECT * FROM `选课管理系统`.`departmentdean`");
			while (rs.next()) {
				depID = rs.getString(1);
				depName = rs.getString(2);
				pw = rs.getString(3);
				depDepart = rs.getString(4);
				DepartmentDeanPO departmentDeanPO = new DepartmentDeanPO(depID,
						depName, pw, depDepart);
				departmentDeanPOs.add(departmentDeanPO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return departmentDeanPOs;
	}

	private ArrayList<UserPO> findAllUniversityDean() {
		ArrayList<UserPO> universityDeanPOs = new ArrayList<UserPO>();
		String uniID = null;
		String uniName = null;
		String pw = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			ResultSet rs = statement
					.executeQuery("SELECT * FROM `选课管理系统`.`universitydean`");
			while (rs.next()) {
				uniID = rs.getString(1);
				uniName = rs.getString(2);
				pw = rs.getString(3);
				UniversityDeanPO universityDeanPO = new UniversityDeanPO(uniID,
						uniName, pw);
				universityDeanPOs.add(universityDeanPO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return universityDeanPOs;
	}

	private int findUserType(String id) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			ResultSet rs = statement
					.executeQuery("SELECT * FROM `选课管理系统`.`student` WHERE stuID='"
							+ id + "'");
			if (rs.next()) {
				return UserPO.STUDENT;
			}
			rs = statement
					.executeQuery("SELECT * FROM `选课管理系统`.`teacher` WHERE teaID='"
							+ id + "'");
			if (rs.next()) {
				return UserPO.TEACHER;
			}
			rs = statement
					.executeQuery("SELECT * FROM `选课管理系统`.`departmentdean` WHERE depID='"
							+ id + "'");
			if (rs.next()) {
				return UserPO.DEPARTMENTDEAN;
			}
			rs = statement
					.executeQuery("SELECT * FROM `选课管理系统`.`universitydean` WHERE uniID='"
							+ id + "'");
			if (rs.next()) {
				return UserPO.UNIVERSITYDEAN;
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1;
	}

	private ResultMessage updateStudent(StudentPO po) {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "UPDATE `选课管理系统`.`student` SET " + "stuName="
					+ getValue(po.getName()) + "," + "stuPassword=" + getValue(po.getPassword())
					+ "," + "departName=" + getValue(po.getDepartmentId()) + ","
					+ "GPA='" + changeToString(po.getGPA()) + "',"
					+ "totalCredit='" + Integer.toString(po.getTotalCredit())
					+ "'," + "totalSelectedCredit='"
					+ Integer.toString(po.getTotalSelectedCredit()) + "',"
					+ "grade='"+Integer.toString(po.getGrade())+"' ";
			sqlString = sqlString + " WHERE `stuID` = '" + po.getId() + "'";
			System.out.println(sqlString);
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			if (e.getErrorCode() == 1452) {
				return ResultMessage.UPDATE_FOREIGNKEY_NOT_EXIST;
			}
			if (e.getErrorCode() == 1062) {
				return ResultMessage.UPDATE_DEPLICATE_PRIMARY;
			}
			if (e.getErrorCode() == 1048) {
				return ResultMessage.UPDATE_ISNULL;
			}
			if (e.getErrorCode() == 1406) {
				return ResultMessage.UPDATE_TOO_LONG;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.UPDATE_SUCCESS;
		} else {
			return ResultMessage.UPDATE_FAILURE;
		}
	}

	private ResultMessage updateTeacher(TeacherPO po) {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "UPDATE `选课管理系统`.`teacher` SET " + "teaName="
					+ getValue(po.getName()) + "," + "teaPassword=" + getValue(po.getPassword())
					+ "," + "departName=" + getValue(po.getDepartmentId()) + " ";
			sqlString = sqlString + " WHERE `teaID` = '" + po.getId() + "'";
			System.out.println(sqlString);
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			if (e.getErrorCode() == 1452) {
				return ResultMessage.UPDATE_FOREIGNKEY_NOT_EXIST;
			}
			if (e.getErrorCode() == 1062) {
				return ResultMessage.UPDATE_DEPLICATE_PRIMARY;
			}
			if (e.getErrorCode() == 1048) {
				return ResultMessage.UPDATE_ISNULL;
			}
			if (e.getErrorCode() == 1406) {
				return ResultMessage.UPDATE_TOO_LONG;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.UPDATE_SUCCESS;
		} else {
			return ResultMessage.UPDATE_FAILURE;
		}
	}

	private ResultMessage updateDepartmentDean(DepartmentDeanPO po) {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "UPDATE `选课管理系统`.`departmentdean` SET "
					+ "depName=" + getValue(po.getName()) + "," + "depPassword="
					+ getValue(po.getPassword()) + "," + "departName="
					+ getValue(po.getDepartmentId()) + " ";
			sqlString = sqlString + " WHERE `depID` = '" + po.getId() + "'";
			System.out.println(sqlString);
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			if (e.getErrorCode() == 1452) {
				return ResultMessage.UPDATE_FOREIGNKEY_NOT_EXIST;
			}
			if (e.getErrorCode() == 1062) {
				return ResultMessage.UPDATE_DEPLICATE_PRIMARY;
			}
			if (e.getErrorCode() == 1048) {
				return ResultMessage.UPDATE_ISNULL;
			}
			if (e.getErrorCode() == 1406) {
				return ResultMessage.UPDATE_TOO_LONG;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.UPDATE_SUCCESS;
		} else {
			return ResultMessage.UPDATE_FAILURE;
		}
	}

	private ResultMessage updateUniversityDean(UniversityDeanPO po) {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "UPDATE `选课管理系统`.`universitydean` SET "
					+ "uniName=" + getValue(po.getName()) + "," + "uniPassword="
					+ getValue(po.getPassword()) + " ";
			sqlString = sqlString + " WHERE `uniID` = '" + po.getId() + "'";
			System.out.println(sqlString);
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			if (e.getErrorCode() == 1452) {
				return ResultMessage.UPDATE_FOREIGNKEY_NOT_EXIST;
			}
			if (e.getErrorCode() == 1062) {
				return ResultMessage.UPDATE_DEPLICATE_PRIMARY;
			}
			if (e.getErrorCode() == 1048) {
				return ResultMessage.UPDATE_ISNULL;
			}
			if (e.getErrorCode() == 1406) {
				return ResultMessage.UPDATE_TOO_LONG;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.UPDATE_SUCCESS;
		} else {
			return ResultMessage.UPDATE_FAILURE;
		}
	}

	private ArrayList<UserPO> findAllStudentByDepartment(String department) {
		ArrayList<UserPO> studentPOs = new ArrayList<UserPO>();
		String stuID = null;
		String stuName = null;
		String pw = null;
		String stuDepart = null;
		int totalCredit = 0;
		int totalSelectedCredit = 0;
		HashMap<Integer, Double> gpa = null;
		int grade = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "SELECT * FROM `选课管理系统`.`student` WHERE student.departName='"
					+ department + "'";
			ResultSet rs = statement.executeQuery(sqlString);
			while (rs.next()) {
				stuID = rs.getString(1);
				stuName = rs.getString(2);
				pw = rs.getString(3);
				stuDepart = rs.getString(4);
				gpa = changeToHashMap(rs.getString(5));
				totalCredit = rs.getInt(6);
				totalSelectedCredit = rs.getInt(7);
				grade = rs.getInt(8);
				StudentPO studentPO = new StudentPO(stuID, stuName, pw,
						stuDepart, totalCredit, totalSelectedCredit, grade);
				studentPO.setGPA(gpa);
				studentPOs.add(studentPO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return studentPOs;
	}

	private ArrayList<UserPO> findAllTeacherByDepartment(String department) {
		ArrayList<UserPO> teacherPOs = new ArrayList<UserPO>();
		String teaID = null;
		String teaName = null;
		String pw = null;
		String teaDepart = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "SELECT * FROM `选课管理系统`.`teacher` WHERE teacher.departName='"
					+ department + "'";
			ResultSet rs = statement.executeQuery(sqlString);
			while (rs.next()) {
				teaID = rs.getString(1);
				teaName = rs.getString(2);
				pw = rs.getString(3);
				teaDepart = rs.getString(4);
				TeacherPO teacherPO = new TeacherPO(teaID, teaName, pw,
						teaDepart);
				teacherPOs.add(teacherPO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return teacherPOs;
	}

	private ArrayList<UserPO> findAllDepartmentDeanByDepartment(
			String department) {
		ArrayList<UserPO> departmentDeanPOs = new ArrayList<UserPO>();
		String depID = null;
		String depName = null;
		String pw = null;
		String depDepart = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "SELECT * FROM `选课管理系统`.`departmentdean` WHERE departmentdean.departName='"
					+ department + "'";
			ResultSet rs = statement.executeQuery(sqlString);
			while (rs.next()) {
				depID = rs.getString(1);
				depName = rs.getString(2);
				pw = rs.getString(3);
				depDepart = rs.getString(4);
				DepartmentDeanPO departmentDeanPO = new DepartmentDeanPO(depID,
						depName, pw, depDepart);
				departmentDeanPOs.add(departmentDeanPO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return departmentDeanPOs;
	}

	@Override
	public String getTeacherID(String teacherName) throws RemoteException {
		String stuID = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sql = "SELECT teacher.teaID FROM `选课管理系统`.`teacher` where teacher.teaName='"
					+ teacherName + "'";
			System.out.println(sql);
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				stuID = rs.getString(1);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return stuID;
	}

	private HashMap<Integer,Double> changeToHashMap(String gpaString){
		HashMap<Integer,Double> hashMap=new HashMap<>();
		if (gpaString==null){
			return hashMap;
		}
		String deleteBraces = gpaString.subSequence(1, gpaString.length()-1).toString();
		String[] elements= deleteBraces.split(", ");
		for (int i=0;i<elements.length;i++){
			String[] subElements=elements[i].split("=");
			if (subElements.length==2){
				hashMap.put(Integer.parseInt(subElements[0]), Double.parseDouble(subElements[1]));
			}
		}
		return hashMap;
	}
	
	private String changeToString (HashMap<Integer, Double> gpaHashMap){
		if (gpaHashMap==null){
			return null;
		}
		return gpaHashMap.toString();
	}

	@Override
	public ArrayList<StudentPO> findStudentPOByTerm(int grade,String department)
			throws RemoteException {
		ArrayList<StudentPO> studentPOs=new ArrayList<>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sql = "SELECT * FROM student WHERE student.`grade`="
					+Integer.toString(grade)+ " AND student.`departName`='"+department+"'";
			System.out.println(sql);
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				StudentPO studentPO=new StudentPO(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(6), rs.getInt(7), rs.getInt(8));
				studentPO.setGPA(changeToHashMap(rs.getString(5)));
				studentPOs.add(studentPO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return studentPOs;
	}

}
